--------------------------------------------------------------------------------
-- @name: parallel dml monitoring
-- @author: dion cho
-- @note: test case for parallel dml monitoring
--------------------------------------------------------------------------------

set echo on
set pages 10000
set lines 200
set timing off
set trimspool on
set serveroutput off


@capture_on

-- create objects
drop table t1 purge;
drop table t2 purge;

create table t1(c1 int, c2 varchar2(100));


insert into t1
select level, rpad('x',100,'x') from dual
connect by level <= 10000
;


create table t2(c1 int, c2 varchar2(100));


insert into t2
select level, rpad('x',100,'x') from dual
connect by level <= 10000
;

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');


----------------------------------------------------------------
-- Case 1: execution plan (10g vs. 11g)
alter session disable parallel dml;

explain plan for 
insert /*+ append parallel(t1 4) */ into t1
select /*+ parallel(t2 4) */ * from t2;

select * from table(dbms_xplan.display);

commit;

alter session enable parallel dml;

explain plan for 
insert /*+ append parallel(t1 4) */ into t1
select /*+ parallel(t2 4) */ * from t2;

select * from table(dbms_xplan.display);

commit;

-- create non unique primary key to t1
alter table t1 
add constraint t1_pk primary key (c1) 
using index(create index t1_n1 on t1(c1));

alter session enable parallel dml;

explain plan for 
insert /*+ append parallel(t1 4) */ into t1
select /*+ parallel(t2 4) */ * from t2;

select * from table(dbms_xplan.display);

commit;

alter table t1 drop constraint t1_pk;

drop index t1_n1;


-----------------------------------------------------------------
-- Case 2: V$PX_PROCESS

-- session #1
-- run slow parallel dml
select userenv('sid') from dual;

alter session enable parallel dml;

insert /*+ append parallel(t1 4) */ into t1
select /*+ parallel(t2 4) */ * from t2;

-- session #2
select
	c.sid as qcsid,
	s.sid as slasid,
	p.server_name 	
from 
	v$session c,
	v$px_session s,
	v$px_process p
where
	c.sid = s.qcsid
	and c.sid = &SID_OF_SESSION_1
	and p.sid = s.sid
;



----------------------------------------------------------------
-- Case 3: SQL*Trace & AWR
alter session enable parallel dml;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);
--exec dbms_monitor.session_trace_enable(waits=>true);

insert /*+ append parallel(t1 4) */ into t1
select /*+ parallel(t2 4) */ * from t2;

commit;

--exec dbms_monitor.session_trace_disable;
exec tpack.end_diag_trace(userenv('sid'), 10046);


alter session disable parallel dml;

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF'));


-- AWR 
@snap_begin

insert /*+ append parallel(t1 4) */ into t1
select /*+ parallel(t2 4) */ * from t2;

commit;

@snap_end
@snap_report


----------------------------------------------------------------
-- Case 3: V$PQ_TQSTAT (10g vs. 11g)

alter session enable parallel dml;

insert /*+ append parallel(t1 4) */ into t1
select /*+ parallel(t2 4) */ * from t2;

commit;

@pq_stat


@capture_off

